﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Net;
using System.Data;
using System.Data.SqlClient;


public partial class Property_Info_Distance : System.Web.UI.Page
{
    List<HiddenField[]> am = new List<HiddenField[]>();
    HiddenField property_lat = new HiddenField();
    HiddenField property_lng = new HiddenField();

    public void Page_Load(object sender, EventArgs e)
    {
        string property_id = "1";
        string parcel_id = getParcelID(property_id);
        string[] fullAddr = getAddress(property_id);

        string address = fullAddr[0] + " " + fullAddr[1] + ", " + fullAddr[3] + " " + fullAddr[2];

        property_lat.ID = "PropertyLat";
        Hid.Parent.Controls.Add(property_lat);

        property_lng.ID = "PropertyLng";
        Hid.Parent.Controls.Add(property_lat);

        List<string> name = getAmenities();
        
        int i = 0;
        string a_string = "";
        string d_string = "";
        string j_string = "";
        
        foreach (string temp in name)
        {  
            HiddenField amnt = new HiddenField();
            amnt.ID = "Amenity_" + i;
            amnt.Value = temp;
            Hid.Parent.Controls.Add(amnt);

            HiddenField dist = new HiddenField();
            dist.ID = "Distance_"+i;
            dist.Value = "0";
            Hid.Parent.Controls.Add(dist);
            
            HiddenField[] ad = new HiddenField[2];
            ad[0] = amnt;
            ad[1] = dist;

            am.Add(ad);
                    
            if(i>0)
            {
                a_string += ",";
                d_string += ",";
                j_string += ",";
            }
            a_string += "\"" + temp + "\"";
            d_string += "0";
            j_string += "\"" + dist.ClientID + "\"";
            i++;
        }

        string js = "<script type=\"text/javascript\">";
        js += "var property_address = \"" + address + "\";\n";
        js += "var property_lat = document.getElementById(\"" + property_lat.ClientID + "\");\n";
        js += "var property_lng = document.getElementById(\"" + property_lng.ClientID + "\");\n";
        js += "var amenities = new Array(" + a_string + ");\n";
        js += "var minDistanceToAmenity = new Array(" + d_string + ");\n";
        js += "var dist_element = new Array(" + j_string + ");\n";
        js += "var store_btn = document.getElementById(\"" + store.ClientID + "\");\n";
        js += "</script>";
        JS.Text = js;
    }
    protected double getDistToCentreOfCity(double lat2, double lat1, double lon2, double lon1)
    {

        int R = 6371; // km
        double dLat = Math.PI * (lat2 - lat1) / 180.0;
        double dLon = Math.PI * (lon2 - lon1) / 180.0;
        double a = Math.Sin(dLat / 2) * Math.Sin(dLat / 2) +
            Math.Cos(Math.PI * lat1 / 180.0) * Math.Cos(Math.PI * lat2 / 180.0) *
            Math.Sin(dLon / 2) * Math.Sin(dLon / 2);
        double c = 2 * Math.Atan2(Math.Sqrt(a), Math.Sqrt(1 - a));
        double d = R * c;
        double distance = d / 1.609344;
        return distance;
    }
    protected void setNearestAmenity(object sender, EventArgs e) 
    {
        SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=property_iq;Integrated Security=SSPI"); 
        try
        {
            conn.Open();
            foreach (HiddenField[] ad in am)
            {     
                string amnt = ad[0].Value;
                string dist = ad[1].Value;
                SqlCommand cmd = new SqlCommand("UPDATE nearest SET distance = '" + dist + "' WHERE amenity = '" + amnt + "'", conn);
                cmd.ExecuteNonQuery();        
            }
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
            }    
        }
        
    }

    protected List<string> getAmenities()
    {
        SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=property_iq;Integrated Security=SSPI");

        SqlDataReader rdr = null;

        List<string> name = new List<string>();

        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("SELECT amenity FROM nearest", conn);

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                name.Add((string)rdr[0]);
            }
        }

        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }

            if (conn != null)
            {
                conn.Close();
            }
        }

        return name;
    }

    protected string getParcelID(string property_id)
    {
        SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=property_iq;Integrated Security=SSPI");

        SqlDataReader rdr = null;

        string parcel_id = "-1";
        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("SELECT parcel_id FROM property WHERE property_id='" + property_id + "'", conn);

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                parcel_id = (string)rdr[0];
            }
        }

        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }

            if (conn != null)
            {
                conn.Close();
            }
        }

        return parcel_id;
    }

    protected string[] getAddress(string property_id)
    {
        SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=property_iq;Integrated Security=SSPI");

        SqlDataReader rdr = null;

        string[] fullAddr = new string[4];

        string address = null;
        string city = null;
        string zip_code = null;
        string state = null;

        try
        {
            conn.Open();

            SqlCommand cmd = new SqlCommand("SELECT address, city, zip_code, state FROM property WHERE property_id='" + property_id + "'", conn);

            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                address = (string)rdr[0];
                city = (string)rdr[1];
                zip_code = (string)rdr[2];
                state = (string)rdr[3];
            }
            fullAddr[0] = address;
            fullAddr[1] = city;
            fullAddr[2] = zip_code;
            fullAddr[3] = state;
        }

        finally
        {
            if (rdr != null)
            {
                rdr.Close();
            }

            if (conn != null)
            {
                conn.Close();
            }
        }

        return fullAddr;
    }
}
